/*
 * Copyright (c) Huawei Technologies Co.,Ltd. 2023. All rights reserved.
 */
package db_time;

import org.opengauss.Logger;
import org.opengauss.driver.DriverInfoManager;
import org.opengauss.driver.IDriverInfo;
import common.testcase.BaseJdbcTestCase2;
import db_time.DbTimeSql.DbTimeStructVo;
import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.opengauss.jdbc.PgConnection;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Arrays;
import java.util.Calendar;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * Title: the DbTimeTest class.
 * <p>
 * Description:
 *
 * @author justbk
 * @version [Tools 0.0.1, 2023/7/21]
 * @since 2023/7/21
 */
public class DbTimeTest extends BaseJdbcTestCase2 {
//    private static final String DB_INFO = "db2";
//    private static final String DB_READ_INFO = DB_INFO + "_r";
    private static final String DB_INFO = "db4";
    private static final String DB_READ_INFO = DB_INFO + "_r";
    private static final double DB_TIME_LEVEL = 0.1;
    @Override
    public String getInfoKey() {
        return DB_INFO;
    }
    
    public static final class DbTimeVo {
        public static String[] keys = {
                "DB_TIME",
                "CPU_TIME",
                "EXECUTION_TIME",
                "PARSE_TIME",
                "PLAN_TIME",
                "REWRITE_TIME",
                "PL_EXECUTION_TIME",
                "PL_COMPILATION_TIME",
                "NET_SEND_TIME",
                "DATA_IO_TIME",
                "SRT1_Q",
                "SRT2_SIMPLE_QUERY",
                "SRT3_ANALYZE_REWRITE",
                "SRT4_PLAN_QUERY",
                "SRT5_LIGHT_QUERY",
                "SRT6_P",
                "SRT7_B",
                "SRT8_E",
                "SRT9_D",
                "SRT10_S",
                "SRT11_C",
                "SRT12_U",
                "SRT13_BEFORE_QUERY",
                "SRT14_AFTER_QUERY",
                "RTT_UNKNOWN"
        };
        private static final int NUMBER_TOTAL = keys.length;
        public String sql;
        public long dbTime;
        public long cpuTime;
        public long executionTime;
        public long parseTime;
        public long planTime;
        public long rewriteTime;
        public long plExecutionTime;
        public long plCompilationTime;
        public long netSendTime;
        public long dataIoTime;
        public long srt1Q;
        public long srt2SimpleQuery;
        public long srt3AnalyzeRewrite;
        public long srt4PlanQuery;
        public long srt5LightQuery;
        public long srt6P;
        public long srt7B;
        public long srt8E;
        public long srt9D;
        public long srt10S;
        public long srt11C;
        public long srt12U;
        public long srt13BeforeQuery;
        public long srt14AfterQuery;
        public long srtUnknown;
    
        @Override
        public String toString() {
            int keyMaxLen = 10;
            String format = Arrays.stream(keys).map(key -> key.substring(0, Math.min(keyMaxLen, key.length())) + "=%-8s")
                    .collect(Collectors.joining(" "));
            return String.format(format, dbTime, cpuTime, executionTime, parseTime, planTime, rewriteTime, plExecutionTime, plCompilationTime,
                    netSendTime, dataIoTime,srt1Q, srt2SimpleQuery, srt3AnalyzeRewrite, srt4PlanQuery, srt5LightQuery, srt6P, srt7B, srt8E, srt9D, srt10S,
                    srt11C, srt12U, srt13BeforeQuery, srt14AfterQuery, srtUnknown);
        }
        
        public boolean check() {
            long calcTotal = executionTime + parseTime + planTime + rewriteTime + plExecutionTime + plCompilationTime + netSendTime + dataIoTime
                    + srt1Q + srt2SimpleQuery + srt3AnalyzeRewrite + srt4PlanQuery + srt5LightQuery + srt6P + srt7B + srt8E + srt9D + srt10S
                    + srt11C + srt12U + srt13BeforeQuery + srt14AfterQuery + srtUnknown;
            String log = "sql=" + sql +  ",total=" + dbTime + ", calcTotal=" + calcTotal+ ",diff = " + (dbTime - calcTotal);
            if (dbTime != calcTotal) {
                Logger.info(log);
                return false;
            }
            double exceptFac = DB_TIME_LEVEL;
            if (srtUnknown * (1 / exceptFac) > dbTime) {
                double actualFac = ((double) srtUnknown) / dbTime;
                log += String.format(",expectFac=%.5f actual=%.5f", exceptFac, actualFac);
                Logger.info(log);
                return false;
            }
            return true;
        }
        
        public static DbTimeVo parse(String sql, long ... args) {
            if (args == null || args.length < NUMBER_TOTAL) {
                return null;
            }
            DbTimeVo timeVo = new DbTimeVo();
            timeVo.sql = sql;
            int index = 0;
            timeVo.dbTime = args[index ++];
            timeVo.cpuTime = args[index ++];
            timeVo.executionTime = args[index ++];
            timeVo.parseTime = args[index ++];
            timeVo.planTime = args[index ++];
            timeVo.rewriteTime = args[index ++];
            timeVo.plExecutionTime = args[index ++];
            timeVo.plCompilationTime = args[index ++];
            timeVo.netSendTime = args[index ++];
            timeVo.dataIoTime = args[index ++];
            timeVo.srt1Q = args[index++];
            timeVo.srt2SimpleQuery = args[index++];
            timeVo.srt3AnalyzeRewrite = args[index++];
            timeVo.srt4PlanQuery = args[index++];
            timeVo.srt5LightQuery = args[index++];
            timeVo.srt6P = args[index++];
            timeVo.srt7B = args[index++];
            timeVo.srt8E = args[index++];
            timeVo.srt9D = args[index++];
            timeVo.srt10S = args[index++];
            timeVo.srt11C = args[index++];
            timeVo.srt12U = args[index++];
            timeVo.srt13BeforeQuery = args[index++];
            timeVo.srt14AfterQuery = args[index++];
            timeVo.srtUnknown = args[index++];
            return timeVo;
        }
    }
    
    public static class DbTimeParse implements ParseResultSet<DbTimeVo> {
        @Override
        public DbTimeVo parse(final ResultSet rs) throws SQLException {
            long [] values = Arrays.stream(DbTimeVo.keys).mapToLong( key -> {
               try {
                   return rs.getLong(key.toLowerCase(Locale.ENGLISH));
               } catch (Exception sqlExp) {
                   sqlExp.printStackTrace();
                   return -1;
               }
            }).toArray();
            return DbTimeVo.parse(rs.getString("query"), values);
        }
    }
    
    private DbTimeSql dbTimeSql;
    private String beginStr;
    private String endStr;
    @Before
    public void setUp() throws SQLException {
//        resetAll();
        dbTimeSql = new DbTimeSql(this);
        dbTimeSql.before();
        dbTimeSql.randomInsert(0, 10);
        Calendar cl = Calendar.getInstance();
        cl.set(2023, 1, 1, 0, 0, 0);
        Timestamp start = new Timestamp(cl.getTime().getTime());
        cl.set(2023, 12, 31, 0, 0, 0);
        Timestamp end = new Timestamp(cl.getTime().getTime());
        PgConnection pgConn = getConn().unwrap(PgConnection.class);
        beginStr = pgConn.getTimestampUtils().toString(null, start);
        endStr = pgConn.getTimestampUtils().toString(null, end);
    }
    
    @After
    public void tearDown() throws SQLException {
        dbTimeSql.after();
    }
    
    @Test
    public void testSimple() throws SQLException {
        int count = 100;
        int total = 0;
        while (count -- > 0) {
            List<DbTimeStructVo> vos = dbTimeSql.queryAll();
            total += vos.size();
        }
        if (total == 0 ) {
            Logger.info("total query:=" + total);
        }
        queryInstrUniqueSql(" where query like '%" + DbTimeSql.TABLE_NAME + "%'");
    }
    
    @Test
    public void testInstrUniqueSqlAll() throws SQLException {
        queryInstrUniqueSql("");
    }
    
    @Test
    public void testPgStatGetWlmSessionInfo() throws SQLException {
        String sql = "select * from pg_stat_get_wlm_session_info(0);";
        queryAll(sql, "");
    }
    
    @Test
    public void testStandbyStatementHistory() throws SQLException {
        String sql = "select * from dbe_perf.standby_statement_history(0)";
        IDriverInfo infoRead = DriverInfoManager.getInfo(DB_READ_INFO);
        try (Connection conn = DriverInfoManager.getConnection(infoRead)) {
            queryAll(conn, sql, "");
        }
    }
    
    @Test
    public void testStandbyStatementHistoryV1() throws SQLException {
        String sql = String.format("select * from dbe_perf.standby_statement_history(0, '%s', '%s')",
                beginStr, endStr);
        IDriverInfo infoRead = DriverInfoManager.getInfo(DB_READ_INFO);
        try (Connection conn = DriverInfoManager.getConnection(infoRead)) {
            queryAll(conn, sql, "");
        }
    }
    
    @Test
    public void testGetGlobalFullSqlByTimeStamp() throws SQLException {
        String sql = String.format("select * from dbe_perf.get_global_full_sql_by_timestamp('%s', '%s')",
                beginStr, endStr);
        queryAll(sql, "");
    }
    
    @Test
    public void testGetGlobalSlowSqlByTimeStamp() throws SQLException {
        String sql = String.format("select * from dbe_perf.get_global_slow_sql_by_timestamp('%s', '%s')",
                beginStr, endStr);
        queryAll(sql, "");
    }
    
    
    @Test
    public void testPvInstanceTime() throws SQLException {
        String sql = "select value from pv_instance_time() order by stat_id;";
        List<Long> results = executeAndGetList(sql, rs -> rs.getLong(1));
        long total = results.stream().mapToLong(val -> val).sum();
        long dbTime =  results.get(0);
        long dbTimeSubTotal = total - results.get(0) - results.get(1);
        long dbUnknown = results.get(results.size() - 1);
        Assert.assertEquals(dbTimeSubTotal, dbTime);
        // 2%
        double exceptFac = 0.02;
        double fac = ((double)dbUnknown)/dbTime;
        System.out.println("dbTime=" + dbTime + ",dbUnknown=" + dbUnknown + ",fac = " + fac);
        Assert.assertTrue(fac < exceptFac);
        Assert.assertTrue(dbUnknown * (1/exceptFac) < dbTime);
    }
    
    private static final String QUERY_COLUMN = "db_time";
    private static final String QUERY_COLUMN_NEW = "rtt_unknown";
    private static final Set<String> ALL_TABLE_VIEWS = new HashSet<String>(){
        {
            add("pg_catalog.gs_wlm_session_query_info_all");
            add("pg_catalog.gs_wlm_session_info_all");
            add("pg_catalog.statement_history");
            add("dbe_perf.statement");
            add("dbe_perf.statement_history");
            add("dbe_perf.summary_statement");
            add("dbe_perf.gs_slow_query_info");
            add("dbe_perf.gs_slow_query_history");
            add("dbe_perf.global_slow_query_history");
            add("dbe_perf.global_slow_query_info");
        }
    };
    
    @Test
    public void testProcAndTableViewNumber() throws SQLException {
        String[] queryFormats = {
                "select count(*) from information_schema.columns where column_name = '%s'",
                "select count(*) from pg_proc where proargnames @> array['%s'];"
        };
        String[] keys = {QUERY_COLUMN, QUERY_COLUMN_NEW};
        for (String queryFormat: queryFormats) {
            int[] numbers = new int[keys.length];
            for (int i = 0; i < keys.length; i++) {
                String sql = String.format(queryFormat, keys[i]);
                numbers[i] = executeAndGetOne(sql, rs -> rs.getInt(1));
            }
            int start = numbers[0];
            for (int i = 1; i < keys.length; i++) {
                Assert.assertEquals("sql=" + queryFormat,start, numbers[i]);
            }
        }
    }
    
    @Test
    public void testAllTableAndViews() throws SQLException {
        String query = "select table_schema, table_name from information_schema.columns where column_name = '" + QUERY_COLUMN + "'";
        List<String[]> results = executeAndGetList(query, rs -> {
            String[] values = new String[2];
            values[0] = rs.getString(1);
            values[1] = rs.getString(2);
            return values;
        });
        Set<String> readTableViews = new HashSet<>();
        for (String[] queryTable: results) {
            readTableViews.add(queryTable[0] + "." + queryTable[1]);
            String sql = String.format("select * from %s.%s", queryTable[0] , queryTable[1]);
            queryAll(sql, "");
        }
        for (String key: ALL_TABLE_VIEWS) {
            if (!readTableViews.contains(key)) {
                Logger.info("unfind object:" + key);
            }
        }
        Assert.assertEquals(ALL_TABLE_VIEWS.size(), results.size());
    }
    
    public void queryInstrUniqueSql(String whereCondition) throws SQLException {
        queryAll("select * from get_instr_unique_sql()", whereCondition);
    }
    
    public void queryAll(String originSql, String whereCondition) throws SQLException {
        queryAll(getConn(), originSql, whereCondition);
    }
    
    public void queryAll(Connection conn, String originSql, String whereCondition) throws SQLException {
        String sql = originSql + " " + whereCondition;
        List<DbTimeVo> results = executeAndGetList(conn, sql, new DbTimeParse());
        if (results.size() == 0) {
            Logger.info("sql=" + sql + ", result.size:" + results.size());
        }
        List<DbTimeVo> matchResults = new LinkedList<>();
        List<DbTimeVo> unmatchResults = new LinkedList<>();
        for (DbTimeVo vo: results) {
            if (!vo.check()) {
                unmatchResults.add(vo);
            } else {
                matchResults.add(vo);
            }
        }
        if (unmatchResults.size() != 0) {
            Logger.info("------------------unmatch:sql");
            for(DbTimeVo vo: unmatchResults) {
                Logger.info(vo.sql);
                Logger.info(vo.toString());
            }
            Logger.info("------------------unmatch_size:" + unmatchResults.size());
        }
        Assert.assertEquals(0, unmatchResults.size());
    }
    
    protected void resetAll() throws SQLException {
        String sql = "select * from reset_unique_sql('local', 'ALL', 0)";
        executeSql(sql);
    }
}
